﻿Option Explicit On
'*******************************************************************
' Program: pkgVisio
' Author: Albert E Edlund
' Date:
'
' Purpose:
' Working with office object XML
'
'*******************************************************************


#Region " imports "

Imports System
Imports System.Object
Imports System.IO
Imports System.IO.TextReader
' need to reference windowsbase in .net
Imports System.IO.Packaging

' need for dbconnectionstringbuilder used in clsConnections
Imports System.Data.Common

Imports System.Windows
Imports System.Windows.Forms
Imports System.Text.StringBuilder

Imports Microsoft.VisualBasic

Imports System.Text
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.ObjectModel

Imports System.Xml
Imports System.Linq
Imports System.Linq.Enumerable
Imports System.Windows.Input

Imports System.Xml.Serialization
Imports System.Xml.Linq
Imports System.Xml.XPath

Imports ADODB
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Sql


Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging


' visio v15 does not use a xName prefix which may put it into conflict with other open office apps
Imports <xmlns="http://schemas.microsoft.com/office/visio/2012/main">
Imports <xmlns:rs="urn:schemas-microsoft-com:rowset">

' generic namespace for office open xml, used in all of them
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

Imports pkgVisio.clsVisPackage
Imports System.Runtime.InteropServices
Imports pkgVisio.clsStructures


#End Region



'**********************************************************************************
'
' Recordset holds the xml schema and datarows 
'
'**********************************************************************************
'
Public Class clsRecordset


#Region " Properties and Declarations "




    '
    ' pkgpartInfo is the collection of content and file pointers in the package
    '
    Private m_colPkgPartInfo As Collection(Of pkgpartInfo)
    Public Property colPkgPartInfo As Collection(Of pkgpartInfo)
        Get
            colPkgPartInfo = m_colPkgPartInfo
        End Get
        Set(value As Collection(Of pkgpartInfo))
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "colPkgPartsInfo is Null.")
            End If
            m_colPkgPartInfo = value
        End Set
    End Property

    '
    '
    '
    Public m_xDoc As XDocument
    Public Property xDoc As XDocument
        Get
            Return Me.m_xDoc
        End Get
        Set(value As XDocument)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "xDoc is Null.")
            End If
            Me.m_xDoc = value
        End Set
    End Property

    '
    '
    '
    Private m_fileCurrent As String
    Public Property fileCurrent As String
        Get
            fileCurrent = m_fileCurrent
        End Get
        Set(value As String)
            m_fileCurrent = value
            If ((value Is Nothing) Or (value = "")) Then
                Throw New System.ArgumentNullException("Value", _
                    "fileCurrent is Null.")
            End If
        End Set
    End Property

    Private _lngRsId As Long
    Public Property lngRsId As Long
        Get
            lngRsId = _lngRsId
        End Get
        Set(value As Long)
            _lngRsId = value
        End Set
    End Property

    Private _lngRsConnId As Long
    Public Property lngRsConnId As Long
        Get
            lngRsConnId = _lngRsConnId
        End Get
        Set(value As Long)
            _lngRsConnId = value
        End Set
    End Property

    Private _strRsName As String
    Public Property strRsName As String
        Get
            strRsName = _strRsName
        End Get
        Set(value As String)
            _strRsName = value
        End Set
    End Property

    Private _nsDict As Dictionary(Of String, System.Xml.Linq.XNamespace)
    Public Property nsDict As Dictionary(Of String, System.Xml.Linq.XNamespace)
        Get
            Return Me._nsDict
        End Get
        Set(value As Dictionary(Of String, System.Xml.Linq.XNamespace))
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "nsDict is Null.")
            End If
            Me._nsDict = value
        End Set
    End Property


#End Region




    '
    '
    '
    Public Sub loadDataSetFromXDoc()

        Try

            Dim aLine As String
            Dim strReader As New StringReader(xDoc.ToString)
            While True
                aLine = strReader.ReadLine
                If aLine.Substring(1, 3) = "- <" Then
                    MsgBox("hyphen start")
                End If
                If aLine Is Nothing Then
                    Exit While
                Else
                    Debug.Print(aLine)
                End If
            End While



            Dim xmlSR As System.IO.StringReader = New System.IO.StringReader("")

            Dim strXml As String = xmlSR.ToString


            Dim dataSet As DataSet = New DataSet
            dataSet.ReadXml(xmlSR, XmlReadMode.IgnoreSchema)
            Dim intTables As Integer = dataSet.Tables.Count
            If intTables > 0 Then
                Dim strTable As String = dataSet.Tables(0).TableName
            End If

        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try
ExitSub:
    End Sub


    '
    ' source is datarecordset
    ' this is where the local schema and data is stored
    '
    Public Sub loadAdoDataToDataGridView _
        (ByRef dgvData As DataGridView)

        Try

            Dim qryAdoData = Me.xDoc...<rs:data>
            Dim intAdoData As Integer = qryAdoData.Count

            Dim rsData As ADODB.Recordset = New ADODB.Recordset
            Dim dsData As System.Data.DataSet = New System.Data.DataSet

            rsData = RecordsetFromXmlString(Me.xDoc.ToString)
            'Debug.Print(rsData.RecordCount.ToString)

            ' make a dataset out of the recordset
            Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
            myDA.Fill(dsData, rsData, strRsName)
            'Debug.Print(dsData.Tables(0).Rows.Count)

            If dgvData IsNot Nothing Then
                ' add the data to the datagridview
                With dgvData
                    .DataSource = dsData
                    .DataMember = strRsName()
                End With
            End If


        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Function RecordsetFromXmlString(strXml As String) _
        As ADODB.Recordset

        Dim rsData As ADODB.Recordset = New ADODB.Recordset
        Dim oStream As ADODB.Stream = New ADODB.Stream


        Try

            oStream.Open()
            oStream.WriteText(strXml)
            oStream.Position = 0
            rsData.Open(oStream)

        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If

        Finally
            If oStream.State = ObjectStateEnum.adStateOpen Then
                oStream.Close()
            End If
            oStream = Nothing
            RecordsetFromXmlString = rsData
            rsData = Nothing
        End Try

    End Function

    '
    '
    '
    Public Function getAdoSchemaFromXDoc _
        (ByVal lclXDoc As XDocument) _
        As String

        Dim strSchema As String = ""

        Dim nsDict As Dictionary(Of String, System.Xml.Linq.XNamespace) = New Dictionary(Of String, System.Xml.Linq.XNamespace)

        Try


            Dim dictResult = lclXDoc.Root.Attributes(). _
                Where(Function(a) a.IsNamespaceDeclaration). _
                GroupBy(Function(a) If(a.Name.[Namespace] = XNamespace.None, [String].Empty, a.Name.LocalName), Function(a) XNamespace.[Get](a.Value)). _
                ToDictionary(Function(g) g.Key, Function(g) g.First())

            nsDict = dictResult

            Dim newXNamespace As XNamespace = ""
            If nsDict.ContainsKey("s") Then
                newXNamespace = nsDict("s")
            End If


            Dim qryAdoSchema = lclXDoc.Descendants(newXNamespace + "Schema")
            Dim intAdoSchema As Integer = qryAdoSchema.Count

            If intAdoSchema = 1 Then
                strSchema = qryAdoSchema.FirstOrDefault.ToString
            End If


        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If

        Finally
            nsDict = Nothing
        End Try

        getAdoSchemaFromXDoc = strSchema

    End Function



#Region " DataTable/XML to Adodb DataRecordset "



    '
    ' pass in a datatable return adodb recordset xml string
    '
    Public Function DataTableToAdoRecordsetXML _
            (ByVal sourceDataTable As System.Data.DataTable) _
            As String

        Dim targetADOXML As String = ""
        Dim targetRecordset As ADODB.Recordset
        Dim targetADOXMLStream As ADODB.Stream

        Try

            targetRecordset = DataTableToADORecordset(sourceDataTable)
            targetADOXMLStream = New ADODB.Stream

            targetRecordset.Save(targetADOXMLStream, _
                ADODB.PersistFormatEnum.adPersistXML)

            targetADOXML = targetADOXMLStream.ReadText( _
                targetADOXMLStream.Size)


        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

        Return targetADOXML


    End Function


    '
    ' source is datarecordset xDocument (data recordset)
    ' output is datatable
    '
    Public Function DataTableFromXMLDataRecordset _
        (ByVal lclXDoc As XDocument, _
         ByVal strRsName As String) _
        As DataTable


        Dim rsData As ADODB.Recordset = New ADODB.Recordset
        Dim dsData As System.Data.DataSet = New System.Data.DataSet
        Dim dtData As System.Data.DataTable = New System.Data.DataTable

        Try

            rsData = RecordsetFromXmlString(lclXDoc.ToString)
            'Debug.Print(rsData.RecordCount.ToString)

            ' make a dataset out of the recordset
            Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
            myDA.Fill(dsData, rsData, strRsName)
            'Debug.Print(dsData.Tables(0).Rows.Count)
            dtData = dsData.Tables(0)
            dtData.TableName = strRsName

        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        DataTableFromXMLDataRecordset = dtData

        If rsData.State = ObjectStateEnum.adStateOpen Then
            rsData.Close()
        End If
        rsData = Nothing
        dtData = Nothing
        dsData = Nothing

        GC.Collect()



    End Function

    '
    ' pass in an datatable return a datarecordset
    '
    Public Function DataTableToADORecordset _
                (ByVal sourceDataTable As System.Data.DataTable) _
                As ADODB.Recordset

        Dim intCt As Integer = sourceDataTable.Rows.Count

        Dim sourceColumn As System.Data.DataColumn
        Dim sourceRow As System.Data.DataRow

        Dim targetRecordset As ADODB.Recordset
        Dim targetFieldAttribute As ADODB.FieldAttributeEnum

        Dim I As Integer

        targetRecordset = New ADODB.Recordset
        Try

            targetRecordset.CursorLocation = ADODB.CursorLocationEnum. _
                adUseClient

            ' iterate through each DataColumn from the source data table
            ' and create a new ADO field for each. 
            For Each sourceColumn In sourceDataTable.Columns

                ' the targetRecordset.Fields.Append method requires
                ' a values for this attribute. 
                If (sourceColumn.AllowDBNull) Then

                    targetFieldAttribute = _
                        ADODB.FieldAttributeEnum.adFldIsNullable
                Else
                    targetFieldAttribute = _
                        ADODB.FieldAttributeEnum.adFldUnspecified
                End If
                targetRecordset.Fields.Append( _
                    sourceColumn.ColumnName, _
                    translateType(sourceColumn.DataType), _
                    sourceColumn.MaxLength, _
                    targetFieldAttribute, _
                    Nothing)
            Next

            If targetRecordset.State = ADODB.ObjectStateEnum.adStateOpen Then
                targetRecordset.Close()
            End If

            ' open the recordset for input.
            targetRecordset.Open( _
                System.Reflection.Missing.Value, _
                System.Reflection.Missing.Value, _
                ADODB.CursorTypeEnum.adOpenStatic, _
                ADODB.LockTypeEnum.adLockOptimistic, _
                0)

            ' for each row in the source DataTable, create a new recordset
            ' row and copy entries to the newly created row
            Dim strSourceType As String = ""
            For Each sourceRow In sourceDataTable.Rows

                targetRecordset.AddNew( _
                    System.Reflection.Missing.Value, _
                    System.Reflection.Missing.Value)

                For I = 0 To sourceDataTable.Columns.Count - 1
                    strSourceType = LCase(sourceRow(I).GetType.ToString)

                    If strSourceType = LCase("Microsoft.SqlServer.Types.SqlHierarchyId") _
                        Or strSourceType = LCase("Microsoft.SqlServer.Types.SqlGeography") Then

                        targetRecordset.Fields(I).Value = sourceRow(I).ToString

                    Else

                        targetRecordset.Fields(I).Value = sourceRow(I)

                    End If

                Next I

            Next

        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        DataTableToADORecordset = targetRecordset

    End Function

    '
    ' translate system type to adodb type
    '
    Private Function translateType _
            (ByVal sourceColumnType As System.Type) _
            As ADODB.DataTypeEnum


        Dim errReturn As ADODB.DataTypeEnum = ADODB.DataTypeEnum.adIUnknown

        Try

            Select Case sourceColumnType.UnderlyingSystemType.ToString()

                Case "System.Boolean"
                    Return ADODB.DataTypeEnum.adBoolean

                Case "System.Byte"
                    Return ADODB.DataTypeEnum.adUnsignedTinyInt

                Case "System.Char"
                    Return ADODB.DataTypeEnum.adChar

                Case "System.DateTime"
                    Return ADODB.DataTypeEnum.adDate

                Case "System.Decimal"
                    Return ADODB.DataTypeEnum.adCurrency

                Case "System.Double"
                    Return ADODB.DataTypeEnum.adDouble

                Case "System.Int16"
                    Return ADODB.DataTypeEnum.adSmallInt

                Case "System.Int32"
                    Return ADODB.DataTypeEnum.adInteger

                Case "System.Int64"
                    Return ADODB.DataTypeEnum.adBigInt

                Case "System.SByte"
                    Return ADODB.DataTypeEnum.adTinyInt

                Case "System.Single"
                    Return ADODB.DataTypeEnum.adSingle

                Case "System.UInt16"
                    Return ADODB.DataTypeEnum.adUnsignedSmallInt

                Case "System.UInt32"
                    Return ADODB.DataTypeEnum.adUnsignedInt

                Case "System.UInt64"
                    Return ADODB.DataTypeEnum.adUnsignedBigInt

                Case "System.String"
                    Return ADODB.DataTypeEnum.adLongVarWChar

                Case "Microsoft.SqlServer.Types.SqlHierarchyId"
                    Return ADODB.DataTypeEnum.adBSTR

                Case "Microsoft.SqlServer.Types.SqlGeography"
                    Return ADODB.DataTypeEnum.adBSTR

                Case Else
                    Return ADODB.DataTypeEnum.adLongVarWChar

            End Select

        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        ' if we got here there was a problem
        Return errReturn

    End Function



#End Region


    '
    '
    '
    Public Sub New()

        Me.m_colPkgPartInfo = Nothing
        Me.colPkgPartInfo = New Collection(Of pkgpartInfo)

        Me.m_xDoc = Nothing
        Me.xDoc = New XDocument


        Me._nsDict = Nothing
        Me.nsDict = New Dictionary(Of String, System.Xml.Linq.XNamespace)



    End Sub



    Protected Overrides Sub Finalize()
        If Me.m_xDoc IsNot Nothing Then
            Me.m_xDoc = Nothing
        End If

        If Me.m_colPkgPartInfo IsNot Nothing Then
            Me.m_colPkgPartInfo.Clear()
            Me.m_colPkgPartInfo = Nothing
        End If

        MyBase.Finalize()
    End Sub


End Class
